--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

DROP TABLE se_gam_artlig;

CREATE TABLE se_gam_artlig AS (SELECT * FROM &erp.gam_artlig WHERE 1=2);


--SELECT * FROM UV_PENT_GAMMES_emploi e  /*gamme standard entete*/
--SELECT * FROM UV_PDET_GAMMES d  /*gamme standard ligne*/
--SELECT * FROM  UV_PPT_GAMMES  /*gamme entete article*/
--SELECT * FROM UV_PPT_GAMMES_DETGAM where id like '%P10832741094%' ORDER BY 1/*gamme ligne article */






 INSERT INTO se_gam_artlig
 (SELECT gene_cod, gene_ind, unfab_cod,artent_ind ,
         Rank() OVER (PARTITION BY gene_cod, gene_ind, unfab_cod,artent_ind ORDER BY Min(ARTLIG_NO) ) artlig_no,
         stdent_cod, stdent_ind, enreg_id, enreg_s, deb_dat, fin_dat, entrestock_ok,
         Rank() OVER (PARTITION BY gene_cod, gene_ind, unfab_cod,artent_ind ORDER BY Min(ARTLIG_NO) ) artlig_rang
  from
    (SELECT SubStr(p.prodbase,1,Length(p.prodbase)-1) gene_cod,
            Nvl(p.indice,'0') gene_ind,
            9099 unfab_cod,
            '0' artent_ind,
            g.RANGMULTI2+1 artlig_no,
            gl.nogamme  stdent_cod,
            nogamme,
            '0' stdent_ind,
            NULL enreg_id,
            'N' enreg_s,
            To_Date('31/12/1967','dd/mm/yyyy')+Nvl(datcre,1) deb_dat,
            NULL fin_dat,
            'N' entrestock_ok,
            g.RANGMULTI2+1 artlig_rang
        FROM UV_PPT_GAMMES a,UV_PPT_GAMMES_DETGAM g,UV_PDET_GAMMES gl,uv_pproduits p
        WHERE p.id=g.id
          AND gl.id=g.detgam
          AND a.id=p.id
        )
    GROUP BY gene_cod, gene_ind, unfab_cod, artent_ind,  stdent_cod, stdent_ind, enreg_id, enreg_s, deb_dat, fin_dat, entrestock_ok
     );


 UPDATE se_gam_artlig tf SET enreg_id=(select enreg_id FROM &erp.gam_artlig f WHERE f.gene_cod=tf.gene_cod AND f.gene_ind=tf.gene_ind AND f.unfab_cod=tf.unfab_cod AND f.artent_ind=tf.artent_ind AND f.artlig_no=tf.artlig_no ) ;
 UPDATE se_gam_artlig tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('gene_cod@gene_ind@unfab_cod@artent_ind@artlig_no','GAM_ARTLIG','O') ;

 commit;
 
 
select * from (
SELECT &outils.stragg(source) source,gene_cod, gene_ind, unfab_cod, artent_ind, artlig_no, stdent_cod, stdent_ind, enreg_id, enreg_s, deb_dat, fin_dat, entrestock_ok, artlig_rang
FROM (
  SELECT 'uv' source ,g.* FROM se_gam_artlig g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.gam_artlig g --where gene_cod ='P10832741094'

  )
GROUP BY gene_cod, gene_ind, unfab_cod, artent_ind, artlig_no, stdent_cod, stdent_ind, enreg_id, enreg_s, deb_dat, fin_dat, entrestock_ok, artlig_rang
HAVING Count(1)!=2) r
ORDER BY gene_cod, gene_ind, unfab_cod,artent_ind,ARTLIG_NO ;